// M H

package ex_dbproduct;

import java.awt.EventQueue;

import javax.print.attribute.HashPrintRequestAttributeSet;
import javax.print.attribute.PrintRequestAttributeSet;
import javax.print.attribute.standard.OrientationRequested;
import javax.swing.JInternalFrame;
import javax.swing.JPanel;

import java.awt.BorderLayout;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.MessageFormat;
import java.util.Vector;

import javax.swing.ButtonGroup;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.BoxLayout;
import javax.swing.SwingConstants;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumnModel;
import javax.swing.table.TableRowSorter;

import com.jgoodies.forms.layout.FormLayout;
import com.jgoodies.forms.layout.ColumnSpec;
import com.jgoodies.forms.layout.RowSpec;
import com.jgoodies.forms.factories.FormFactory;

import java.awt.FlowLayout;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
import java.awt.print.PrinterException;
import java.awt.event.KeyAdapter;
import java.awt.event.KeyEvent;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.io.File;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import javax.swing.event.InternalFrameAdapter;
import javax.swing.event.InternalFrameEvent;

public class CodeList extends JInternalFrame {
	Connection con;
	
	DefaultTableModel model;
	ResultSetMetaData rsm;
//	ButtonGroup bg = new ButtonGroup();
	Vector<String> col = new Vector<String>();
//	Vector<String> vec = new Vector<String>();
	DecimalFormat df = new DecimalFormat("###,###,###.##");
	ProductMain5 m;
	
	private JPanel panel;
	private JPanel panel_1;
	private JPanel panel_2;
	private JScrollPane scrollPane;
	private JTable table;
	private JTextField textField;
	private JButton btnNewButton;
	private JPanel panel_3;
	private JPanel panel_4;
	private JPanel panel_5;
	private JPanel panel_6;
	private JLabel lblNewLabel;
	private JTextField textField_1;
	private JLabel lblNewLabel_1;
	private JTextField textField_2;
	private JLabel lblNewLabel_2;
	private JTextField textField_3;
	private JLabel lblNewLabel_3;
	private JTextField textField_4;
	private JLabel lblNewLabel_4;
	private JTextField textField_5;
	private JLabel lblNewLabel_5;
	private JTextField textField_6;
	private JLabel lblNewLabel_6;
	private JTextField textField_7;
	private JLabel lblNewLabel_7;
	JTextField textField_8;
	private JButton btnNewButton_1;
	private JButton btnNewButton_2;
	private JPanel panel_7;
	private JButton btnNewButton_3;
	private JButton btnNewButton_4;	
	private JPanel panel_8;

//	/**
//	 * Launch the application.
//	 */
//	public static void main(String[] args) {
//		EventQueue.invokeLater(new Runnable() {
//			public void run() {
//				try {
//					System.out.println("여기오냐 ?");
//					New_List frame = new New_List(con);
//					frame.setVisible(true);
//				} catch (Exception e) {
//					e.printStackTrace();
//				}
//			}
//		});
//	}

	/**
	 * Create the frame.
	 */
	public CodeList(Connection c, ProductMain5 ma) {		
		super("제품 조회", true, true, true, false);
		
		this.con = c;
		this.m = ma;
		
		addInternalFrameListener(new InternalFrameAdapter() {
			@Override
			public void internalFrameClosed(InternalFrameEvent arg0) {
				m.codeList = null;
			}
		});
		setTitle("제품 조회");		
//		setLayout(null);
//		JInternalFrame frame = new JInternalFrame();
//		frame.setSize(200, 300);
//		frame.setLocation(100, 50);
//		frame.setVisible(true);
		setBounds(100, 100, 866, 455);
//		add(frame);
//		setSize(500, 500);
//		setVisible(true);
		getContentPane().add(getPanel(), BorderLayout.CENTER);
		getContentPane().add(getPanel_1(), BorderLayout.EAST);

		col.add("제품 코드");
		col.add("제품 이름");
		col.add("제품 원가");
		col.add("등록자");
		col.add("등록일");
		col.add("수정자");
		col.add("수정일");
		col.add("거래처");
		model = new DefaultTableModel(col, 0) {
			public boolean isCellEditable(int rowIndex, int mColIndex) {
				return false;
			}
		};
		
	}
	public void search() {		
		String find = textField.getText();
		String sql = "SELECT * FROM CODE WHERE PRO_CODE LIKE '%" + find
				+ "%' or PRO_NM LIKE '%" + find + "%' ORDER BY PRO_CODE";
		try {
			model.setRowCount(0);
			PreparedStatement pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();

			int columnCnt = rsmd.getColumnCount();

			while (rs.next()) {
				Vector<String> row = new Vector<String>();
				row.add(rs.getString(1)); // 제품 코드
				row.add(rs.getString(2)); // 제품 이름
//				row.add(rs.getString(3)); // 제품 원가

				row.add(df.format(Integer.parseInt(rs.getString(3))) + "원"); // 단가
				row.add(rs.getString(5)); // 등록자
				row.add(rs.getString(4)); // 등록일
				row.add(rs.getString(7)); // 수정자
				row.add(rs.getString(6)); // 수정일
				row.add(rs.getString(8)); // 거래처
				model.addRow(row);

			}
			table.setModel(model);
			DefaultTableCellRenderer Center = new DefaultTableCellRenderer();
			Center.setHorizontalAlignment(SwingConstants.CENTER); // 가운데 정렬
			TableColumnModel tcmSchedule = table.getColumnModel();
			DefaultTableCellRenderer celAlignRight = new DefaultTableCellRenderer();
			celAlignRight.setHorizontalAlignment(JLabel.RIGHT);

			tcmSchedule.getColumn(0).setCellRenderer(Center);
			tcmSchedule.getColumn(1).setCellRenderer(Center);
			tcmSchedule.getColumn(2).setCellRenderer(celAlignRight);
			tcmSchedule.getColumn(3).setCellRenderer(Center);
			tcmSchedule.getColumn(4).setCellRenderer(Center);
			tcmSchedule.getColumn(5).setCellRenderer(Center);
			tcmSchedule.getColumn(6).setCellRenderer(Center);
			tcmSchedule.getColumn(7).setCellRenderer(Center);

			rs.close();
			pstmt.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	public void mouseR() {
		int row = table.getSelectedRow();
		textField_1.setText(("" + table.getValueAt(row, 0)));
		textField_2.setText(("" + table.getValueAt(row, 1)));
		textField_3.setText(("" + table.getValueAt(row, 2)));
		textField_4.setText(("" + table.getValueAt(row, 3)));
		String str = "" + table.getValueAt(row, 4);
		String str2 = str.substring(2, 16);
		textField_5.setText(str2);		
		textField_6.setText(("" + table.getValueAt(row, 5)));		
		String str3 = "" + "" + table.getValueAt(row, 6);
		String str4 = str3.substring(2, 16);
		textField_7.setText(str4);		
		textField_8.setText(("" + table.getValueAt(row, 7)));
	}
	public void modNm() {
		String c = textField_1.getText();
		String str = textField_2.getText();
		String cs = textField_8.getText();
		try {
			String sql = "UPDATE CODE SET PRO_NM =?, PRO_CS_NO =?, PRO_MOD_DATE = SYSDATE WHERE PRO_CODE =?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, str);
			pstmt.setString(2, cs);
			pstmt.setString(3, c);
			pstmt.executeUpdate();
			JOptionPane.showMessageDialog(null, "수정되었습니다.");
			search();

			pstmt.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	public void ExcelSave(JTable table) {// 엑셀 저장하는 메소드(매개변수 JTable)
		try {
			JFileChooser jfc = new JFileChooser();
			int yn = jfc.showSaveDialog(null);
			if (yn == 0) {
				String fileName = jfc.getSelectedFile().toString();// 저장경로 받기
				WritableWorkbook workbook = Workbook.createWorkbook(new File(
						fileName + ".xls"));

				WritableSheet s1 = workbook.createSheet("sheet 0", 0); // 엑셀에
																		// 시트생성

				for (int i = 0; i < table.getColumnCount(); i++) {// 열 제목 추가
					Label label = new Label(i, 0, table.getColumnName(i));
					s1.addCell(label);
				}

				// 데이터 저장
				for (int i = 0; i < table.getRowCount(); i++) {// 행 추가
					for (int j = 0; j < table.getColumnCount(); j++) {
						Label label = new Label(j, i + 1,
								(String) table.getValueAt(i, j)); // 라벨객체 생성.
						s1.addCell(label);
					}
				}

				JOptionPane
						.showMessageDialog(null, fileName + ".xls 에 저장되었습니다");// 알림창
				workbook.write(); // 파일로 쓰기
				workbook.close(); // 닫기
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	private JPanel getPanel() {
		if (panel == null) {
			panel = new JPanel();
			panel.setLayout(new BorderLayout(0, 0));
			panel.add(getPanel_2(), BorderLayout.NORTH);
			panel.add(getScrollPane(), BorderLayout.CENTER);
			panel.add(getPanel_8_1(), BorderLayout.SOUTH);
		}
		return panel;
	}
	private JPanel getPanel_1() {
		if (panel_1 == null) {
			panel_1 = new JPanel();
			panel_1.setLayout(new BorderLayout(0, 0));
			panel_1.add(getPanel_3(), BorderLayout.NORTH);
			panel_1.add(getPanel_4(), BorderLayout.CENTER);
		}
		return panel_1;
	}
	private JPanel getPanel_2() {
		if (panel_2 == null) {
			panel_2 = new JPanel();
			panel_2.setLayout(new BorderLayout(0, 0));
			panel_2.add(getTextField());
			panel_2.add(getBtnNewButton(), BorderLayout.EAST);
		}
		return panel_2;
	}
	private JScrollPane getScrollPane() {
		if (scrollPane == null) {
			scrollPane = new JScrollPane();
			scrollPane.setViewportView(getTable());
		}
		return scrollPane;
	}
	private JTable getTable() {
		if (table == null) {
			table = new JTable(model);
			table.addMouseListener(new MouseAdapter() {
				@Override
				public void mouseReleased(MouseEvent arg0) {
					mouseR();
				}
			});
		}
		return table;
	}
	private JTextField getTextField() {
		if (textField == null) {
			textField = new JTextField();			
			textField.addKeyListener(new KeyAdapter() {
				@Override
				public void keyReleased(KeyEvent arg0) {
					if (arg0.getKeyCode() == KeyEvent.VK_ENTER) {
						search();
					}
				}
			});
			textField.setColumns(10);
		}
		return textField;
	}
	private JButton getBtnNewButton() {
		if (btnNewButton == null) {
			btnNewButton = new JButton("조회");
			btnNewButton.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent arg0) {
					search();					
				}
			});
		}
		return btnNewButton;
	}
	private JPanel getPanel_3() {
		if (panel_3 == null) {
			panel_3 = new JPanel();
			panel_3.setLayout(new BorderLayout(0, 0));
			panel_3.add(getPanel_7(), BorderLayout.EAST);
		}
		return panel_3;
	}
	private JPanel getPanel_4() {
		if (panel_4 == null) {
			panel_4 = new JPanel();
			panel_4.setLayout(new BorderLayout(0, 0));
			panel_4.add(getPanel_5_1(), BorderLayout.CENTER);
			panel_4.add(getPanel_6(), BorderLayout.SOUTH);
		}
		return panel_4;
	}
	private JPanel getPanel_5_1() {
		if (panel_5 == null) {
			panel_5 = new JPanel();
			panel_5.setLayout(new FormLayout(new ColumnSpec[] {
					FormFactory.RELATED_GAP_COLSPEC,
					FormFactory.DEFAULT_COLSPEC,
					FormFactory.RELATED_GAP_COLSPEC,
					ColumnSpec.decode("default:grow"),},
				new RowSpec[] {
					FormFactory.RELATED_GAP_ROWSPEC,
					FormFactory.DEFAULT_ROWSPEC,
					FormFactory.RELATED_GAP_ROWSPEC,
					FormFactory.DEFAULT_ROWSPEC,
					FormFactory.RELATED_GAP_ROWSPEC,
					FormFactory.DEFAULT_ROWSPEC,
					FormFactory.RELATED_GAP_ROWSPEC,
					FormFactory.DEFAULT_ROWSPEC,
					FormFactory.RELATED_GAP_ROWSPEC,
					FormFactory.DEFAULT_ROWSPEC,
					FormFactory.RELATED_GAP_ROWSPEC,
					FormFactory.DEFAULT_ROWSPEC,
					FormFactory.RELATED_GAP_ROWSPEC,
					FormFactory.DEFAULT_ROWSPEC,
					FormFactory.RELATED_GAP_ROWSPEC,
					FormFactory.DEFAULT_ROWSPEC,}));
			panel_5.add(getLblNewLabel(), "2, 2, right, default");
			panel_5.add(getTextField_1(), "4, 2, fill, default");
			panel_5.add(getLblNewLabel_1(), "2, 4, right, default");
			panel_5.add(getTextField_2(), "4, 4, fill, default");
			panel_5.add(getLblNewLabel_2(), "2, 6, right, default");
			panel_5.add(getTextField_3(), "4, 6, fill, default");
			panel_5.add(getLblNewLabel_3(), "2, 8, left, default");
			panel_5.add(getTextField_4(), "4, 8, fill, default");
			panel_5.add(getLblNewLabel_4(), "2, 10, left, default");
			panel_5.add(getTextField_5(), "4, 10, fill, default");
			panel_5.add(getLblNewLabel_5(), "2, 12, left, default");
			panel_5.add(getTextField_6(), "4, 12, fill, default");
			panel_5.add(getLblNewLabel_6(), "2, 14, left, default");
			panel_5.add(getTextField_7(), "4, 14, fill, default");
			panel_5.add(getLblNewLabel_7(), "2, 16, left, default");
			panel_5.add(getTextField_8(), "4, 16, fill, default");
		}
		return panel_5;
	}
	private JPanel getPanel_6() {
		if (panel_6 == null) {
			panel_6 = new JPanel();
			panel_6.setLayout(new BorderLayout(0, 0));
			panel_6.add(getBtnNewButton_3(), BorderLayout.WEST);
			panel_6.add(getBtnNewButton_4(), BorderLayout.EAST);
		}
		return panel_6;
	}
	private JLabel getLblNewLabel() {
		if (lblNewLabel == null) {
			lblNewLabel = new JLabel("제품 코드");
		}
		return lblNewLabel;
	}
	private JTextField getTextField_1() {
		if (textField_1 == null) {
			textField_1 = new JTextField();
			textField_1.setEditable(false);
			textField_1.setColumns(10);
		}
		return textField_1;
	}
	private JLabel getLblNewLabel_1() {
		if (lblNewLabel_1 == null) {
			lblNewLabel_1 = new JLabel("제품 이름");
		}
		return lblNewLabel_1;
	}
	private JTextField getTextField_2() {
		if (textField_2 == null) {
			textField_2 = new JTextField();
			textField_2.setColumns(10);
		}
		return textField_2;
	}
	private JLabel getLblNewLabel_2() {
		if (lblNewLabel_2 == null) {
			lblNewLabel_2 = new JLabel("제품 원가");
		}
		return lblNewLabel_2;
	}
	private JTextField getTextField_3() {
		if (textField_3 == null) {
			textField_3 = new JTextField();
			textField_3.setEditable(false);
			textField_3.setColumns(10);
		}
		return textField_3;
	}
	private JLabel getLblNewLabel_3() {
		if (lblNewLabel_3 == null) {
			lblNewLabel_3 = new JLabel("등록자");
		}
		return lblNewLabel_3;
	}
	private JTextField getTextField_4() {
		if (textField_4 == null) {
			textField_4 = new JTextField();
			textField_4.setEditable(false);
			textField_4.setColumns(10);
		}
		return textField_4;
	}
	private JLabel getLblNewLabel_4() {
		if (lblNewLabel_4 == null) {
			lblNewLabel_4 = new JLabel("등록일");
		}
		return lblNewLabel_4;
	}
	private JTextField getTextField_5() {
		if (textField_5 == null) {
			textField_5 = new JTextField();
			textField_5.setEditable(false);
			textField_5.setColumns(10);
		}
		return textField_5;
	}
	private JLabel getLblNewLabel_5() {
		if (lblNewLabel_5 == null) {
			lblNewLabel_5 = new JLabel("수정자");
		}
		return lblNewLabel_5;
	}
	private JTextField getTextField_6() {
		if (textField_6 == null) {
			textField_6 = new JTextField();
			textField_6.setEditable(false);
			textField_6.setColumns(10);
		}
		return textField_6;
	}
	private JLabel getLblNewLabel_6() {
		if (lblNewLabel_6 == null) {
			lblNewLabel_6 = new JLabel("수정일");
		}
		return lblNewLabel_6;
	}
	private JTextField getTextField_7() {
		if (textField_7 == null) {
			textField_7 = new JTextField();
			textField_7.setEditable(false);
			textField_7.setColumns(10);
		}
		return textField_7;
	}
	private JLabel getLblNewLabel_7() {
		if (lblNewLabel_7 == null) {
			lblNewLabel_7 = new JLabel("거래처");
		}
		return lblNewLabel_7;
	}
	private JTextField getTextField_8() {
		if (textField_8 == null) {
			textField_8 = new JTextField();
			textField_8.addMouseListener(new MouseAdapter() {
				@Override
				public void mouseReleased(MouseEvent arg0) {
					// 거래처 검색 팝업 화면
					String csNo = textField_8.getText();
					CsChange cs = new CsChange(con, csNo, CodeList.this);
					// 수정사항
					cs.setVisible(true);
					cs.setBounds(400, 200, 334, 346);
				}
			});
			textField_8.setColumns(10);
		}
		return textField_8;
	}
	private JButton getBtnNewButton_1() {
		if (btnNewButton_1 == null) {
			btnNewButton_1 = new JButton("엑셀");
			btnNewButton_1.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent arg0) {
					ExcelSave(table);
				}
			});
		}
		return btnNewButton_1;
	}
	private JButton getBtnNewButton_2() {
		if (btnNewButton_2 == null) {
			btnNewButton_2 = new JButton("인쇄");
			btnNewButton_2.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent arg0) {
					try {
						MessageFormat headerFormat = new MessageFormat("프린트테스트"); // 타이틀
						MessageFormat footerFormat = new MessageFormat("- {0} -"); // 페이지(자동 증가 된다.)
						PrintRequestAttributeSet aset = new HashPrintRequestAttributeSet(); // 프린트 설정(용지를 가로로 할지 세로로 할지)
						aset.add(OrientationRequested.LANDSCAPE); // 용지 디폴트를 가로로
						table.print(JTable.PrintMode.FIT_WIDTH, headerFormat, footerFormat, true, aset, true); // JTable 붙인다.
					} catch (PrinterException pe) {
						System.err.println("Error printing: " + pe.getMessage());
					}
				}
			});
		}
		return btnNewButton_2;
	}
	private JPanel getPanel_7() {
		if (panel_7 == null) {
			panel_7 = new JPanel();
			panel_7.setLayout(new BorderLayout(0, 0));
			panel_7.add(getBtnNewButton_1(), BorderLayout.WEST);
			panel_7.add(getBtnNewButton_2(), BorderLayout.EAST);
		}
		return panel_7;
	}
	private JButton getBtnNewButton_3() {
		if (btnNewButton_3 == null) {
			btnNewButton_3 = new JButton("    수정    ");
			btnNewButton_3.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent arg0) {
					modNm();
				}
			});
		}
		return btnNewButton_3;
	}
	private JButton getBtnNewButton_4() {
		if (btnNewButton_4 == null) {
			btnNewButton_4 = new JButton("    삭제    ");
			btnNewButton_4.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent arg0) {
					
				}
			});
		}
		return btnNewButton_4;
	}
	private JPanel getPanel_8_1() {
		if (panel_8 == null) {
			panel_8 = new JPanel();
		}
		return panel_8;
	}
}
